import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import result_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def result_dxhdgzfx_iot(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)
    sql = f'''insert into fridge_biz_result.result_dxhdgzfx_iot
                (
                select
                    upper(t2.model) as model, t1.material,t1.fault_name,t1.fault_quantity,t1.fault_total,t3.all_num as access_total,t1.rn, t1.time
                from
                    (
                        select
                            material,
                            fault_name,
                            count(1) as fault_quantity,
                            sum(count(1)) over() as fault_total,
                            row_number() over(PARTITION BY material order by count(1) DESC ) AS rn,
                            format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                        from
                        (
                            select
                                distinct objectid,
                                if(zzfld00000b is null,'',substr(zzfld00000b,4,7)) as material,
                                if(zzfld00002gt is null,'',zzfld00002gt) as fault_name
                            from
                                ods.kyd_ods t1
                            join
                                ods.device t2
                            on
                                t1.zzfld00000b = t2.sn
                            where
                                length(t1.zzfld00000b) =24
                            and
                                date(t1.p_date)>=(date('{date}')  - interval '12' month)
                            and
                                date(t1.p_date) < date('{date}') 
                            and
                                substr(t1.zzfld00000b,4,7) is not null
                            and
                                substr(t1.zzfld00000b,4,7) != ''
                            and
                                t1.zzfld000001='QT30'
                            and
                                t2.type = 'FRIDGE'
                        )
                        group by
                            material,fault_name
                        order by
                            material desc,fault_quantity desc ,rn
                    ) t1
                left join
                    dim.material t2
                on t1.material = t2.material
                left join
                    (select material,count(1) as all_num from ods.device where type = 'FRIDGE' group by material) t3
                on
                    t1.material = t3.material
                where
                    t2.model is not null
                and
                    t2.model != ''
                and
                    t2.model != ' '
                and
                    t1.rn <= 10
                )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_dxhdgzfx_iot(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

